
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;

import main.util.JdbcUtil;
import main.util.SqlConstants;
import main.util.StringUtil;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;


public class ExcelChangeID {

	//public static FileWriter staffInfo,role,serviceType,password,orm;
	static FileWriter del;
	
	public static Map<String,Integer> address;
	
	public static JdbcUtil db=new JdbcUtil();
	
	public static Map<String,String> profixs=new HashMap<String, String>();
	public static Map<String,String> orgaIDs=new HashMap<String, String>();
	public static Map<String,Integer> nums=new HashMap<String, Integer>();
	
	
	public static void main(String[] args) {
		InputStream inp;
		try {
			inp = new FileInputStream("D:/workbook34.xlsx");
			OutputStream out = new FileOutputStream("D:/workbook00.xlsx");
			
			Workbook wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(2);
			
			String profixStr="BJ-北京市;SH-上海市;TJ-天津市;CQ-重庆市;HE-河北省;SX-山西省;NM-内蒙古自治区;LN-辽宁省;JL-吉林省;HL-黑龙江省;JS-江苏省;"
						+ "ZJ-浙江省;AH-安徽省;FJ-福建省;JX-江西省;SD-山东省;HA-河南省;HB-湖北省;HN-湖南省;GD-广东省;GX-广西壮族自治区;HI-海南省; SC-四川省;"
						+ "GZ-贵州省;YN-云南省;XZ-西藏自治区;SN-陕西省;GS-甘肃省;QH-青海省;NX-宁夏回族自治区;XJ-新疆维吾尔族自治区";
			putString(profixStr,profixs);
			String orgaIDStr="000300010014-北京运营管理组;000300020011-天津运营管理组;000300030008-内蒙古营管理组;000300040016-河北运营管理组;"
					+ "000300050008-黑龙江运营管理组;000300060009-辽宁运营管理组;000300070007-吉林运营管理组;000300080010-山东运营管理组;"
					+ "000300090017-河南运营管理组;000300100012-山西运营管理组;000300110007-陕西运营管理组;000300120011-甘肃运营管理组;000300130008-宁夏运营管理组;"
					+ "000300140009-新疆运营管理组;000300150005-西藏运营管理组;000300160015-云南运营管理组;000300170011-四川运营管理组;000300180015-重庆运营管理组;"
					+ "000300190006-湖北运营管理组;000300200009-湖南运营管理组;000300210016-江苏运营管理组;000300220007-江西运营管理组;000300230010-浙江运营管理组;"
					+ "000300240009-福建运营管理组;000300250008-广东运营管理组;000300270015-贵州运营管理组;000300280010-上海运营管理组;000300290008-海南运营管理组;"
					+ "000300300009-安徽运营管理组;000300310008-青海运营管理组";
			putString(orgaIDStr,orgaIDs);
			
			
			 operateSheet(sheet);
			 wb.write(out);
			 
			 wb.close();
			 inp.close();
			 out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			db.close();
		}
	}
	
	public static void operateSheet(Sheet sheet){
		for(int i=0;i<sheet.getLastRowNum()+1;i++){
			Row row = sheet.getRow(i);
			setRow(row,i+1);
		}
	}
	
	public static void setRow(Row row,int i){
		String orgaName=SqlConstants.getColValue(row, 2);
		String orgaId=getString(orgaName,orgaIDs);
		String profix=getString(orgaName,profixs);
		
		if(StringUtil.isEmpty(orgaId) || StringUtil.isEmpty(orgaName)){
			System.out.println(i+":失败");
			return;
		}
		
		//row.getCell(9).setCellValue(orgaId);
		SqlConstants.setColValue(row, 9, orgaId);
		SqlConstants.setColValue(row, 10, "acmp");
		//row.getCell(10).setCellValue("acmp");
		
		String channel=SqlConstants.getColValue(row, 8);
		String staffid=getStaffId(profix,channel);
		SqlConstants.setColValue(row, 0, staffid);
		System.out.println(i+":"+staffid+"成功");
	}
	
	
	public static String getStaffId(String profix,String channel){
		String staffId="";
		String newprofix=profix;
		
		int start=80001;
		if(!channel.trim().equals("自有")){
			newprofix=profix+"F";
			start=10001;
		}
		
		if(nums.get(newprofix)==null){
			nums.put(newprofix, start);
		}else{
			start=nums.get(newprofix);
		}
		
		staffId=newprofix+start;
		nums.put(newprofix, nums.get(newprofix)+1);
		if(!channel.trim().equals("自有")){
			return staffId.replace(newprofix+"1", newprofix);
		}
		return staffId;
	}
	
	public static String getString(String name,Map<String,String> indexMap){
		String value="";
		for(String index:indexMap.keySet()){
			if(index.indexOf(name)>-1){
				value=indexMap.get(index);
				break;
			}
		}
		return value;
	}
	
	public static void putString(String index,Map<String,String> indexMap){
		String[] args=index.split(";");
		for(String arg:args){
			String[] pys=arg.split("-");
			if(pys.length!=2){
				continue;
			}
			indexMap.put(pys[1].trim(), pys[0].trim());
		}
	}
	
}
